﻿DECLARE @DATE_END_MONTH Date=CONVERT(DATE,(SELECT MAX_DATE_ENDMONTH FROM [DATE_EM_REPORT]))
DECLARE @LAST_MONTH NVARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(M,-1,CONVERT(DATE,@DATE_END_MONTH)),112)
DECLARE @LAST_2_MONTH NVARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(M,-2,CONVERT(DATE,@DATE_END_MONTH)),112)
--PRINT @LAST_MONTH
DECLARE @MONTH NVARCHAR(100) = (SELECT MAX_MONTH FROM DATE_EM_REPORT)

PRINT ('DANH SACH DU KIEN BADBANK')

EXEC('
DELETE FROM LIST_TOPDOWN_RAW
DELETE FROM TBL_ALERT_BAD_BANK_TOPDOWN

--drop table TBL_ALERT_BAD_BANK_TOPDOWN

INSERT LIST_TOPDOWN_RAW
SELECT B.CIF, A.[COLOR], B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME, A.MONTH
--INTO LIST_TOPDOWN_RAW
FROM GIANGLH3.[TAICAPNHANH].[DBO].[TOPDOWN_LUYKE] A, TBL_CUSTOMER B
WHERE CUSTOMER_ID COLLATE DATABASE_DEFAULT =B.CIF 
--AND Color = ''RED'' 
AND A.MONTH in ('+@MONTH+','+@LAST_MONTH+','+@LAST_2_MONTH+')


INSERT TBL_ALERT_BAD_BANK_TOPDOWN
select CONVERT(DATE,'''+@DATE_END_MONTH+''') AS BUSINESS_DATE, CIF, CUS_NAME, BRANCH_ID, INDUSTRY_NAME, INDUSTRY_NAME_EN, DAO, DAO_NAME, ['+@MONTH+'] AS MONTH, ['+@LAST_MONTH+'] AS MONTH1,['+@LAST_2_MONTH+'] AS MONTH2
from 
(SELECT *
FROM LIST_TOPDOWN_RAW ) AS A
PIVOT
(MAX(COLOR) FOR MONTH IN (['+@MONTH+'],['+@LAST_MONTH+'],['+@LAST_2_MONTH+'])) AS PVT')


update b
set b.date_data = (select MAX_DATE_ENDMONTH from DATE_EM_REPORT)
from TBL_DATE_BUSINESS b
where b.name_table = 'TBL_ALERT_BAD_BANK_TOPDOWN'


--select * from TBL_ALERT_BAD_BANK_TOPDOWN where dao = '2677' order by cif
--SELECT * FROM GIANGLH3.[TAICAPNHANH].[DBO].[TOPDOWN_LUYKE]
--
PRINT ('DANH SACH WATCHLIST')


EXEC('
DELETE FROM TBL_ALERT_BAD_BANK_WATCHLIST

INSERT TBL_ALERT_BAD_BANK_WATCHLIST
SELECT B.CIF, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME,'''+@LAST_MONTH+''' AS MONTH
FROM [GIANGLH3].[MISDECK].[DBO].[WATCHLIST_'+@LAST_MONTH+'] A, TBL_CUSTOMER B
WHERE A.CIF COLLATE DATABASE_DEFAULT = B.CIF

INSERT TBL_ALERT_BAD_BANK_WATCHLIST
SELECT B.CIF, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME,'''+@MONTH+''' AS MONTH FROM [GIANGLH3].[MISDECK].[DBO].[WATCHLIST_'+@MONTH+'] A, TBL_CUSTOMER B
WHERE A.CIF COLLATE DATABASE_DEFAULT = B.CIF
')

PRINT ('DANH SACH BOTTOMUP')

EXEC('
DELETE FROM TBL_ALERT_BADBANK_BOTTOMUP

INSERT TBL_ALERT_BADBANK_BOTTOMUP
SELECT B.CIF, CASE  
WHEN A.Color = ''XANH'' THEN ''GREEN''
WHEN A.Color = ''ĐỎ'' THEN ''RED''
WHEN A.Color = ''VÀNG'' THEN ''YELLOW'' ELSE ''RED'' END COLOR
, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME ,''201610'' AS MONTH
FROM GIANGLH3.TAICAPNHANH.[DBO].[BOTTOMUP_luyke] A, TBL_CUSTOMER B
WHERE A.CIF COLLATE DATABASE_DEFAULT = B.CIF AND A.Color IS NOT NULL and a.month = ''201610''


INSERT TBL_ALERT_BADBANK_BOTTOMUP
SELECT B.CIF, CASE  
WHEN A.Color = ''XANH'' THEN ''GREEN''
WHEN A.Color = ''ĐỎ'' THEN ''RED''
WHEN A.Color = ''VÀNG'' THEN ''YELLOW'' ELSE ''RED'' END COLOR
, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME ,''201702'' AS MONTH
FROM GIANGLH3.TAICAPNHANH.[DBO].[BOTTOMUP_luyke] A, TBL_CUSTOMER B
WHERE A.CIF COLLATE DATABASE_DEFAULT = B.CIF AND A.Color IS NOT NULL and a.month = ''201702''
')

